import { forEach } from 'lodash-es';
import { compact, difference, unzip } from 'underscore';
import * as XLSX from 'xlsx';
import Studycodemodel from '../mysql_interface/studycodemodel';
const _SheetJSFT = [
    "xlsx",
    "xlsb",
    "xlsm",
    "xls",
    "xml",
    "csv",
    "txt",
    "ods",
    "fods",
    "uos",
    "sylk",
    "dif",
    "dbf",
    "prn",
    "qpw",
    "123",
    "wb*",
    "wq*",
    "html",
    "htm"
]

export const Sheetjsftstring = _SheetJSFT.map(function (x) {
    return "." + x;
}).join(",");


export function getExceltodownload(data: unknown[][], name: string) {
    const ws = XLSX.utils.aoa_to_sheet(data)
    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, name);
    // @ts-ignore
    XLSX.write(wb, { type: "file", bookType: "xlsx", file: `${name}.xlsx` });
}
// 合并单元格
export function getExcelMerges(data: unknown[][], name: string, merges: { s: { r: number; c: number; }; e: { r: number; c: number; }; }[], headerRows: number) {
    const ws = XLSX.utils.aoa_to_sheet(data)
    ws["!merges"] = merges;
    ws["!freeze"] = {
        ySplit: headerRows,
    }
    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, name);
    // @ts-ignore
    XLSX.write(wb, { type: "file", bookType: "xlsx", file: `${name}.xlsx` });
}

/**
 * //多个 sheet
 * @param data 
 * @param name 
 */
export function getExceltodownload2(data: Record<string, unknown[][]>, name: string) {
    const wb = XLSX.utils.book_new();
    forEach(data, (e, k) => {
        const ws = XLSX.utils.aoa_to_sheet(e)
        XLSX.utils.book_append_sheet(wb, ws, k);
    })
    // @ts-ignore
    XLSX.write(wb, { type: "file", bookType: "xlsx", file: `${name}.xlsx` });
}


/**
 * 
 * @param file 文件
 * @param start 开始行数
 * @param end 结束行数
 * @returns 数据
 */
export function getfiletodata2<T = unknown>(file: File, start = 0, end = Infinity): Promise<T[][]> {
    return new Promise((res, rej) => {
        const reader = new FileReader();
        reader.onload = e => {
            const _obj: Record<string, string> = {

            }
            for (let n = 97; n > 123; n++) {
                _obj[String.fromCharCode(n).toLocaleUpperCase()] = ''
            }
            /* Parse data */
            if (!e.target) return
            const bstr = e.target.result;

            const wb = XLSX.read(bstr, {
                type: "binary"
            });
            /* Get first worksheet */
            const wsname = wb.SheetNames[0];

            const ws = wb.Sheets[wsname];
            /* Convert array of arrays */
            const data = XLSX.utils.sheet_to_json<T[]>(ws, {
                header: 'A'
            });
            console.log(data)
            const keys = Object.keys(data.map(e => {
                return Object.assign(_obj, e)
            })[start - 1])
            res(data.slice(start, end).map(e => {
                if (Array.isArray(e)) return e
                return keys.map(t => e[t])
            }))
        };
        reader.readAsBinaryString(file);
    })

}



export function getfiletodata<T = unknown>(file: File, arr: string[]): Promise<T[][]> {
    return new Promise((res, rej) => {
        const reader = new FileReader();
        reader.onload = e => {
            /* Parse data */
            if (!e.target) return
            const bstr = e.target.result;

            const wb = XLSX.read(bstr, {
                type: "binary"
            });
            /* Get first worksheet */
            const wsname = wb.SheetNames[0];
            const ws = wb.Sheets[wsname];
            /* Convert array of arrays */
            const data = XLSX.utils.sheet_to_json<T[]>(ws, {
                header: 1
            });
            /* Update state */
            const mess = inspects(data[0] ? data[0] : [], arr);
            if (mess) {
                const t = data.slice(1, Infinity)
                res(t)
            } else {
                rej('文件字段缺失')
            }
        };
        reader.readAsBinaryString(file);
    })

}

const make_cols = (refstr: string) =>
    Array(XLSX.utils.decode_range(refstr).e.c + 1)
        .fill(0)
        .map((x, i) => ({
            name: XLSX.utils.encode_col(i),
            key: i
        }));

// 检查文件内容字段是否缺失
function inspects(row: unknown[], arr: string[]) {
    const nuarr = difference(arr, row);
    if (nuarr.length !== 0) {
        return false
    }
    return true;
}



/**
 * 导出比赛成绩按照成绩
*/
export function ActivityExcal1(option: Studycodemodel.Iuser[], name: string, team: Studycodemodel.Iteam[] = [], teamuser: Studycodemodel.Iteamuser[] = []) {
    const arr: (string | number | undefined)[] = ['用户名称', '用户账号', '团队', '总得分']
    const t: (string | number | undefined)[][] = []
    option.forEach((e, key) => {
        const { matchuserquestion, ...user } = e
        const usermatch = matchuserquestion
        if (key === 0) {
            usermatch?.forEach((v, k) => {
                arr.push(k + 1)
            })
            t.push(arr)
        }
        let zdf = 0
        const tmp = usermatch?.map((v) => {
            const { df = 0 } = v
            zdf = df + zdf
            return v.df
        }) || []
        const _teamuser = teamuser.filter(e => e.userid === user.ID).map(z => z.teamid)
        const teamname = team.filter(z => z.userid === user.ID || _teamuser.includes(z.ID)).map(f => f.name).join(',')
        t.push([user.name, user.ID, teamname, zdf, ...tmp])
    })
    t.sort((a, b) => {
        if (a[2] === '总得分') {
            return Infinity
        }
        return Number(b[3]) - Number(a[3])
    })
    const js = unzip(t.slice(1, Infinity)).map((e, k) => {
        if (k < 3) {
            return '正确率'
        }
        return Math.floor(compact(e).length / e.length * 100)
    })
    t.push(js)
    getExceltodownload(t, name)
}


/**
 * 导出比赛成绩按照成绩
*/
export function ActivityExcal2(option: Studycodemodel.Iuser[], name: string, team: Studycodemodel.Iteam[] = [], teamuser: Studycodemodel.Iteamuser[] = []) {
    const arr: (string | number | undefined)[] = ['用户名称', '用户账号', '总得分']
    const t: (string | number | undefined)[][] = []
    option.forEach((e, key) => {
        const { matchuserquestion, ...user } = e
        const usermatch = matchuserquestion
        if (key === 0) {
            usermatch?.forEach((v, k) => {
                arr.push(k + 1)
            })
            t.push(arr)
        }
        let zdf = 0
        const tmp = usermatch?.map((v) => {
            const { df = 0, info, resultdataarr } = v
            zdf = df + zdf
            if (Array.isArray(info)) {
                return `${df} (${resultdataarr?.join(',')})`
            }
            return `${df}`
        }) || []
        const _teamuser = teamuser.filter(e => e.userid === user.ID).map(z => z.teamid)
        const teamname = team.filter(z => z.userid === user.ID || _teamuser.includes(z.ID)).map(f => f.name).join(',')
        t.push([user.name, user.ID, teamname, zdf, ...tmp])
    })
    t.sort((a, b) => {
        if (a[3] === '总得分') {
            return Infinity
        }
        return Number(b[3]) - Number(a[3])
    })
    const js = unzip(t.slice(1, Infinity)).map((e, k) => {
        if (k < 3) {
            return '正确率'
        }
        return Math.floor(compact(e).length / e.length * 100)
    })
    t.push(js)
    getExceltodownload(t, name)
}